package letsjava.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Observable;

public class NSDB  extends Observable{
	private String dbName;
	private String dbDriver = "com.mysql.jdbc.Driver";
	private String dbUrl = "jdbc:mysql://127.0.0.1:3306/";
	private String dbUser = "root";
	private String dbPassword = "";
	private Connection conn;

	public NSDB(String dbName) {
		this.dbName = dbName;
	}

	public boolean connect() {
		try {
			// 加载驱动程序
			Class.forName(dbDriver);
			// 连续数据库
			conn = DriverManager.getConnection(dbUrl + dbName, dbUser,
			        dbPassword);
			if (!conn.isClosed()) {
				sendNotify("use database "+dbName);
				return false;
			} else {
				return true;
			}
		} catch (Exception e) {
			System.out.print(e.toString());
			return false;
		}
	}

	public boolean colse() {
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public ArrayList<String> getFields(ResultSet rs) throws SQLException{
		ResultSetMetaData metaData = rs.getMetaData();
        int rowCount = metaData.getColumnCount();
        ArrayList<String> fields = new  ArrayList<String>();
        for (int i = 0; i < rowCount; i++) {
        	fields.add(metaData.getColumnName(i + 1));
        }
        return fields;
	}
	
	public ArrayList<HashMap> query(String sql)
	        throws SQLException {
		sendNotify(sql);
		Statement statement = conn.createStatement();
		ArrayList<HashMap> list = new ArrayList<HashMap>();
		ResultSet rs = statement.executeQuery(sql);
		
		ArrayList<String> header = getFields(rs);
		while (rs.next()) {
			HashMap line = new HashMap();
			for (String field : header) {
				line.put(field, rs.getString(field));
			}
			list.add(line);
		}
		return list;
	}

	public void exec(String sql) throws SQLException {
		sendNotify(sql);
		Statement statement = conn.createStatement();
		statement.execute(sql);
	}
	
	protected void sendNotify(String a){
		setChanged();
		notifyObservers(a);
	}
	public void execBind(String sql, String[] param)
	        throws SQLException {
		execBind(sql, Arrays.asList(param));
	}
	public void execBind(String sql, List<String> param)
	        throws SQLException {
		sendNotify(sql + param.toString());
		PreparedStatement pstmt = conn.prepareStatement(sql);
		for (int i = 0; i < param.size(); i++) {
			pstmt.setString(i + 1, param.get(i));
		}
		pstmt.execute();
	}

	public ArrayList<HashMap> queryBind(String sql, String[] param) throws SQLException {
		return queryBind(sql,Arrays.asList(param));
	}
	public ArrayList<HashMap> queryBind(String sql, List<String> param) throws SQLException {
		sendNotify(sql + param.toString());

		ArrayList<HashMap> list = new ArrayList<HashMap>();
		PreparedStatement pstmt = conn.prepareStatement(sql);
		for (int i = 0; i < param.size(); i++) {
			pstmt.setString(i + 1, param.get(i));
			//System.out.println(i + 1);
			//System.out.println(param.get(i));
		}

		ResultSet rs = pstmt.executeQuery();
		ArrayList<String> header = getFields(rs);
		while (rs.next()) {
			HashMap line = new HashMap();
			for (String field : header) {
				line.put(field, rs.getString(field));
			}
			list.add(line);
		}
		return list;
	}
}
